---------------------------------------------------
-- @name: dbms_xplan.display
-- @author: dion cho
-- @description: test for dbms_xplan.display function
---------------------------------------------------

-- check version
select * from v$version;

-- create objects
drop table t_plan purge;

create table t_plan(c1 varchar2(10));

create index t_plan_idx on t_plan(c1);

-- generate data
insert into t_plan
select 'Many1' 
from dual
connect by level <= 10000;

commit;

exec dbms_stats.gather_table_stats(user, 't_plan', -
    cascade=>true, no_invalidate=>false);
    
-- change data a lot
insert into t_plan
select 'Many2'
from dual
connect by level <= 10000;

commit;


-- Poor execution plan
explain plan for 
select count(*) 
from t_plan
where c1 = 'Many2';

select * from table(dbms_xplan.display);


-- Check with gather_plan_statistics hint
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Many2';

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- Regather statistics
exec dbms_stats.gather_table_stats(user, 't_plan', -
    cascade=>true, no_invalidate=>false);


-- Check with gather_plan_statistics hint
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Many2';

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats last'));



-- Check various options for dbms_xplan.display_cursor

-- basic format
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'basic'));


-- typical format
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'typical'));


-- all format
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'all'));


-- outline format
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'outline'));


-- advanced format
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'advanced'));


-- allstats
-- execute several times and see how stats increases
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats'));

-- allstats last
-- execute several times
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- peeked binds
var b1 varchar2(10);
exec :b1 := 'Few';

select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = :b1;

select * from 
table(dbms_xplan.display_cursor
(null, null, 'allstats last +peeked_binds'));


-- misc options
select /*+ gather_plan_statistics */ 
    count(*)
from t_plan
where c1 = 'Few';

select * from 
table(dbms_xplan.display_cursor(null, null, 'iostats last'));


select /*+ gather_plan_statistics */ 
    count(*)
from 
  t_plan t1, 
  t_plan t2
where 
  rownum <= 100000
;

select * from 
table(dbms_xplan.display_cursor(null, null, 'memstats last'));


-- sql id and child number
column sql_id new_value v_sql_id
column child_number new_value v_child_number

select sql_id, child_number, sql_text
from v$sql
where sql_text like 'select /*+ gather_plan_statistics */%';

select * from 
table(dbms_xplan.display_cursor('&v_sql_id', &v_child_number, 'allstats')); 
-- or allstats last


-- Join with v$sqlarea and retrieve more meaningful data
select
  p.plan_table_output
from
  (select distinct sql_id, child_number
      from v$sql_plan s
    where s.object_name = 'T_PLAN_IDX') s,
  table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'typical')) p
;

-------------------------------------------------------------------------
-- gather_plan_statistics and parallel query
-- try both on 10g and 11g (and 9i if possible?)

-- execute parallel select
alter table t_plan parallel 4;

select /*+ gather_plan_statistics */ count(*) 
from t_plan
;

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- check with sql id
select * from 
table(dbms_xplan.display_cursor('&sql_id', null, 'allstats last'));

-- check v$sql
select sql_id, child_number, executions, px_servers_executions
from v$sql 
where sql_id = '&sql_id';

-- execute parallel dml
alter session enable parallel dml;
insert /*+ gather_plan_statistics */ into t_plan select * from t_plan t;

select * from 
table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- check with sql id
select * from 
table(dbms_xplan.display_cursor('&sql_id', null, 'allstats last'));

-- check v$sql
select sql_id, child_number, executions, px_servers_executions
from v$sql 
where sql_id = '&sql_id';

-- check v$sql_shared_cursor
set serveroutput on

exec print_table('select * from v$sql_shared_cursor where sql_id = ''&sql_id''');

set serveroutput off
